Fork me on GitHub

Deploying Group Replication in Single-Primary Mode

发表于 | 分类于 ， ， | | 阅读次数
| 字数统计 1,195 | 阅读时长预计 6

本文描述了3实例MySQL Group Replication的搭建过程

MGR

一、环境信息

ip地址 主机名 server_id
172.17.84.71 mysql001 1
172.17.84.72 mysql002 2
172.17.84.73 mysql003 3

二、搭建前准备

  1. 关闭selinux(略)

  2. 开启端口3306 33061

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ##Add
    firewall-cmd --permanent --zone=public --add-port=3306/tcp
    firewall-cmd --permanent --zone=public --add-port=33061/tcp
    ##Reload
    firewall-cmd --reload
     
    ## 检查是否生效
    firewall-cmd --zone=public --query-port=3306/tcp
    firewall-cmd --zone=public --query-port=33061/tcp
     
    ## 列出所有的开放端口
    firewall-cmd --list-all

  3. 配置/etc/hosts ip和主机名对应关系

    1
    2
    3
    4
    [root@mysql003 ~]# cat /etc/hosts
    172.17.84.71 mysql001
    172.17.84.72 msyql002
    172.17.84.73 mysql003

三、初始化三个数据库实例

3.1 初始化3个mysql实例

mysql001配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[root@mysql001 ~]# cat /usr/local/mysql/etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql/run/mysql.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/run/mysql.sock
pid_file = /usr/local/mysql/run/mysql.pid
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
max_allowed_packet = 512M
max_connections = 2048
open_files_limit = 65535
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 2048M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 64M
log-error = /usr/local/mysql/log/mysql_error.log
log-bin = /usr/local/mysql/binlogs/mysql-bin
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log
long_query_time = 5
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
server-id=1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum=NONE
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.17.84.71:33061"
loose-group_replication_group_seeds= "172.17.84.71:33061,172.17.84.72:33061,172.17.84.73:33061"
loose-group_replication_bootstrap_group= off
loose-group_replication_ip_whitelist='172.17.84.71,172.17.84.72,172.17.84.73'

参数说明见Group Replication System Variables

初始化实例 并启动

1
2
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
systemctl start mysqld

到错误日志文件中找到临时密码进行登录，登录后修改临时密码

1
2
[root@mysql001 /]# grep 'temporary password' /usr/local/mysql/log/mysql_error.log
2017-04-24T03:37:44.558511Z 1 [Note] A temporary password is generated for root@localhost: 3yFK,#qtjAl;

修改root密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password('admin_123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 150
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified

修改密码操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错。全新的环境可以通过reset master解决这个问题。

1
2
[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
[Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

四、配置MGR

4.1 创建复制账号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

4.2 使用change master命令配置server

在下次需要从其他成员恢复其状态时，使用group_replication_recovery复制通道的给定凭据

1
2
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'  FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

4.3 安装复制组插件

1
2
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.26 sec)

4.4 查看插件是否安装成功

1
2
3
4
5
6
7
8
9
10
11
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
.
.
.
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

4.5、配置引导组，并启动GROUP_REPLICATION

此引导应仅有单个server独立完成，该server启动组并且只启动一次

1
2
3
4
5
6
#### 设置group_replication_bootstrap_group 只需要在mysql001上执行一次，另外两个实例不执行这句
mysql> SET GLOBAL group_replication_bootstrap_group=ON;  
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

查看log，发现是白名单问题导致的，my.cnf添加白名单后重新启动组复制

1
2
3
4
5
6
7
8
mysql> set global group_replication_ip_whitelist = '172.17.84.71,172.17.84.72,172.17.84.73';
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.02 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

直接添加白名单到my.cnf,防止下次启动再重新问题

1
loose-group_replication_ip_whitelist='172.17.84.71,172.17.84.72,172.17.84.73'

4.6 查看状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 60b61f19-289f-11e7-b97d-08002730b4d8 | mysql001    |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.01 sec)
mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 434
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1
1 row in set (0.00 sec)

4.7 向组中添加实例mysql002 mysql003

mysql002 mysql003的操作和mysql001相同，除了不需要SET GLOBAL group_replication_bootstrap_group=ON;

1
2
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.02 sec)

查看最终状态

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 80af8598-1520-11e7-a8b9-08002730b4d8 | mysql001    |        3306 | ONLINE       |
| group_replication_applier | 8abf4eab-1521-11e7-9cc9-080027b95fc4 | mysql002    |        3306 | ONLINE       |
| group_replication_applier | dcd3068d-15bc-11e7-b264-080027dad0d6 | mysql003    |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

4.8 修改 group_replication_start_on_boot=on

1
loose-group_replication_start_on_boot=on

参考

  1. Deploying Group Replication in Single-Primary Mode
  2. http://www.niugebbs.com/HRT152/1244148.html
  3. http://blog.csdn.net/dbaxiaosa/article/details/70226540
  4. MySQL Group Replication多机多实例安装配置
  5. MySQL Group Replication 9节点快速部署
好记性不如烂笔头，生命不息，学习不止！

分享